"""
Version: 0.1
Author: CarpeDiem
Date: 2022/9/6
Description: 合并表格数据
"""

import pandas as pd
from sqlalchemy import create_engine
'''1.堆叠不同时间的订单详情表'''
# 建立连接
conn = create_engine(
    'mysql+pymysql://127.0.0.1:root@127.0.0.1:3306/data?charset=utf8')

# 读取数据
detail1 = pd.read_sql('meal_order_detail1', conn)
detail2 = pd.read_sql('meal_order_detail2', conn)
detail3 = pd.read_sql('meal_order_detail3', conn)
# 纵向堆叠3张表
detail = detail1.append(detail2)
detail = detail.append(detail3)
print('3张订单详情表合并后的形状为:', detail.shape)
'''2.主键合并订单详情表、订单信息表和客户信息表'''
order = pd.read_csv('../data/meal_order_info.csv', sep=',',
                    encoding='gb18030')  # 读取订单信息表
user = pd.read_excel('../data/users_info.xlsx')  # 读取用户信息表
# 数据类型转换，存储部分数据
order['info_id'] = order['info_id'].astype('str')
order['emp_id'] = order['emp_id'].astype('str')
user['USER_ID'] = user['USER_ID'].astype('str')
data = pd.merge(detail,
                order,
                left_on=['order_id', 'emp_id'],
                right_on=['info_id', 'emp_id'])
data = pd.merge(data, user, left_on='emp_id', right_on='USER_ID', how='inner')
print('三张表数据主键合并后的大小为:', data.shape)